﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HttpUploader4.demoSql2005.db
{
	/// <summary>
	/// 数据库访问操作
	/// 更新记录：
	///		2012-4-10 创建
	/// </summary>
	public class HttpUploaderDB
	{
        string m_dbServer = @"localhost";	//数据库地址
		string m_dbUser = "sa";			//数据库帐号
		string m_dbPass = "123456";			//数据库密码
        string m_dbName = "ApolloUpload";	//数据库名称
		string m_dbTable = "xdb_files";		//数据表名称

		/// <summary>
		/// 获取数据库连接字符串
		/// 示例："Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;
		/// </summary>
		/// <returns></returns>
		string GetConStr()
		{
            string conStr = "Data Source=%server%;Initial Catalog=%database%;Integrated Security=SSPI;";
            //string conStr = "Data Source=%server%;Initial Catalog=%database%;User Id=%uid%;Password=%pwd%;";
			conStr = conStr.Replace("%server%", this.m_dbServer);
			conStr = conStr.Replace("%database%", this.m_dbName);
            //conStr = conStr.Replace("%uid%", this.m_dbUser);
            //conStr = conStr.Replace("%pwd%", this.m_dbPass);

			return conStr;
		}

		public SqlConnection GetCon()
		{
			return new SqlConnection(this.GetConStr());
		}
		
		/// <summary>
		/// 根据UID获取文件列表
		/// </summary>
		/// <param name="uid"></param>
		/// <param name="tb"></param>
        public void GetFilesByUid(string systemId, string moduleId, string belongId, ref DataTable tb)
		{
            string sql = "select * from xdb_files where systemId=@systemId and moduleId=@moduleId and belongId=@belongId and IsDeleted=0;";
			SqlConnection con = this.GetCon();
			SqlDataAdapter ad = new SqlDataAdapter(sql, con);
            ad.SelectCommand.Parameters.Add("@systemId", SqlDbType.NVarChar);
            ad.SelectCommand.Parameters[0].Value = systemId;
            ad.SelectCommand.Parameters.Add("@moduleId", SqlDbType.NVarChar);
            ad.SelectCommand.Parameters[1].Value = moduleId;
            ad.SelectCommand.Parameters.Add("@belongId", SqlDbType.NVarChar);
            ad.SelectCommand.Parameters[2].Value = belongId;
			con.Open();
			ad.Fill(tb);
			con.Close();
		}

		/// <summary>
		/// 根据文件ID获取文件信息
		/// 说明：
		///		由于此SQL执行频繁非常高，所以强烈建议改为存储过程。
		/// </summary>
		/// <param name="fid"></param>
		/// <returns></returns>
		public bool GetFileInfByFid(int fid,ref xdb_files inf)
		{
			bool ret = false;
			SqlConnection con = this.GetCon();
			SqlCommand cmd = con.CreateCommand();
			//string sql = "select top 1 * from xdb_files where fid=@fid";
			cmd.CommandText = "spGetFileInfByFid";
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@fid", SqlDbType.Int);
			cmd.Parameters[0].Value = fid;
			cmd.Connection.Open();
			SqlDataReader r = cmd.ExecuteReader();
			if (r.Read())
			{
				inf.fid = fid;
                inf.uid = r.GetInt32(1);
                inf.SystemId = r.GetString(2);
                inf.ModuleId = r.GetString(3);
                inf.BelongId = r.GetString(4);
				inf.FileNameLocal = r.GetString(5);
				inf.FileNameRemote = r.GetString(6);
				inf.FilePathLocal = r.GetString(7);
				inf.FilePathRemote = r.GetString(8);
				inf.FilePathRelative = r.GetString(9);
				inf.FileMD5 = r.GetString(10);
				inf.FileLength = Convert.ToInt64(r.GetString(11));
				inf.FileSize = r.GetString(12);
				inf.FilePos = Convert.ToInt64(r.GetString(13));
				inf.PostedLength = Convert.ToInt64(r.GetString(14));
				inf.PostedPercent = r.GetString(15);
				inf.PostComplete = r.GetBoolean(16);
				inf.PostedTime = r.GetDateTime(17);
				inf.IsDeleted = r.GetBoolean(18);
				ret = true;
				r.Close();
			}
			cmd.Connection.Close();
			return ret;
		}

		/// <summary>
		/// 根据文件MD5获取文件信息
		/// </summary>
		/// <param name="md5"></param>
		/// <param name="inf"></param>
		/// <returns></returns>
		public bool GetFileInfByMd5(string md5, ref xdb_files inf)
		{
			bool ret = false;
			SqlConnection con = this.GetCon();
			SqlCommand cmd = con.CreateCommand();
            string sql = "select top 1 fid,uid,SystemId,ModuleId,BelongId,FileNameLocal,FileNameRemote,FilePathLocal,FilePathRemote,FilePathRelative,FileMD5,FileLength,FileSize,FilePos,PostedLength,PostedPercent,PostComplete,PostedTime,IsDeleted from xdb_files where FileMD5=@FileMD5";
			cmd.CommandText = sql;
			cmd.Parameters.Add("@FileMD5", SqlDbType.VarChar, 32);
			cmd.Parameters[0].Value = md5;
			cmd.Connection.Open();
			SqlDataReader r = cmd.ExecuteReader();
			if (r.Read())
			{
				inf.fid = r.GetInt32(0);
                inf.uid = r.GetInt32(1);
                inf.SystemId = r.GetString(2);
                inf.ModuleId = r.GetString(3);
                inf.BelongId = r.GetString(4);
				inf.FileNameLocal = r.GetString(5);
				inf.FileNameRemote = r.GetString(6);
				inf.FilePathLocal = r.GetString(7);
				inf.FilePathRemote = r.GetString(8);
				inf.FilePathRelative = r.GetString(9);
				inf.FileMD5 = r.GetString(10);
				inf.FileLength = Convert.ToInt64(r.GetString(11));
				inf.FileSize = r.GetString(12);
				inf.FilePos = Convert.ToInt64(r.GetString(13));
				inf.PostedLength = Convert.ToInt64(r.GetString(14));
				inf.PostedPercent = r.GetString(15);
				inf.PostComplete = r.GetBoolean(16);
				inf.PostedTime = r.GetDateTime(17);
				inf.IsDeleted = r.GetBoolean(18);
				ret = true;
				r.Close();
			}
			cmd.Connection.Close();
			return ret;
		}

		/// <summary>
		/// 增加一条数据，并返回新增数据的ID
		/// 在ajax_create_fid.aspx中调用
		/// 文件名称，本地路径，远程路径，相对路径都使用原始字符串。
		/// d:\soft\QQ2012.exe
		/// </summary>
		public int Add(ref xdb_files model)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("insert into xdb_files(");
            strSql.Append("FileSize,FilePos,PostedLength,PostedPercent,PostComplete,PostedTime,IsDeleted,uid,FileNameLocal,FileNameRemote,FilePathLocal,FilePathRemote,FilePathRelative,FileMD5,FileLength,SystemId,ModuleId,BelongId");
			strSql.Append(") values (");
            strSql.Append("@FileSize,@FilePos,@PostedLength,@PostedPercent,@PostComplete,@PostedTime,@IsDeleted,@uid,@FileNameLocal,@FileNameRemote,@FilePathLocal,@FilePathRemote,@FilePathRelative,@FileMD5,@FileLength,@SystemId,@ModuleId,@BelongId");
            strSql.Append(");select @@identity;");

			SqlConnection con = this.GetCon();
			SqlCommand cmd = con.CreateCommand();
			cmd.CommandText = strSql.ToString();
			cmd.Parameters.Add("@FileSize", SqlDbType.VarChar,10);
			cmd.Parameters.Add("@FilePos", SqlDbType.VarChar, 19);
			cmd.Parameters.Add("@PostedLength", SqlDbType.VarChar, 19);
			cmd.Parameters.Add("@PostedPercent", SqlDbType.VarChar, 6);
			cmd.Parameters.Add("@PostComplete", SqlDbType.Bit, 1);
			cmd.Parameters.Add("@PostedTime", SqlDbType.DateTime);
			cmd.Parameters.Add("@IsDeleted", SqlDbType.Bit, 1);
			cmd.Parameters.Add("@uid", SqlDbType.Int, 4);
			cmd.Parameters.Add("@FileNameLocal", SqlDbType.VarChar, 255);
			cmd.Parameters.Add("@FileNameRemote", SqlDbType.VarChar, 255);
			cmd.Parameters.Add("@FilePathLocal", SqlDbType.VarChar,512);
			cmd.Parameters.Add("@FilePathRemote", SqlDbType.VarChar,512);
			cmd.Parameters.Add("@FilePathRelative", SqlDbType.VarChar,512);
			cmd.Parameters.Add("@FileMD5", SqlDbType.VarChar, 32);
            cmd.Parameters.Add("@FileLength", SqlDbType.VarChar, 19);
            cmd.Parameters.Add("@SystemId", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@ModuleId", SqlDbType.NVarChar, 10);
            cmd.Parameters.Add("@BelongId", SqlDbType.NVarChar, 10);
            
			cmd.Parameters[0].Value = model.FileSize;
			cmd.Parameters[1].Value = model.FilePos.ToString();
			cmd.Parameters[2].Value = model.PostedLength.ToString();
			cmd.Parameters[3].Value = model.PostedPercent;
			cmd.Parameters[4].Value = model.PostComplete;
			cmd.Parameters[5].Value = model.PostedTime;
			cmd.Parameters[6].Value = model.IsDeleted;
			cmd.Parameters[7].Value = model.uid;
			cmd.Parameters[8].Value = model.FileNameLocal;
			cmd.Parameters[9].Value = model.FileNameRemote;
			cmd.Parameters[10].Value = model.FilePathLocal;
			cmd.Parameters[11].Value = model.FilePathRemote;
			cmd.Parameters[12].Value = model.FilePathRelative;
			cmd.Parameters[13].Value = model.FileMD5;
            cmd.Parameters[14].Value = model.FileLength.ToString();
            cmd.Parameters[15].Value = model.SystemId;
            cmd.Parameters[16].Value = model.ModuleId;
            cmd.Parameters[17].Value = model.BelongId;
			cmd.Connection.Open();
            object fid = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			cmd.Connection.Close();
			return Convert.ToInt32(fid);
		}

		/// <summary>
		/// 更新上传进度
		/// 说明：
		///		由于此函数的SQL语句执行频率非常高，所以强烈建议改为存储过程。
		/// </summary>
		///<param name="uid">用户ID</param>
		///<param name="fid">文件ID</param>
		///<param name="filePos">文件位置，大小可能超过2G，所以需要使用long保存</param>
		///<param name="postedLength">已上传长度，文件大小可能超过2G，所以需要使用long保存</param>
		///<param name="postedPercent">已上传百分比</param>
		public bool UpdateProgress(int uid,int fid,long filePos,long postedLength,string postedPercent)
		{
			//string sql = "update xdb_files set FilePos=@FilePos,PostedLength=@PostedLength,PostedPercent=@PostedPercent where uid=@uid and fid=@fid";
			SqlConnection con = this.GetCon();
			SqlCommand cmd = con.CreateCommand();
			cmd.CommandText = "spUpdateProcess";
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@FilePos", SqlDbType.VarChar,19);
			cmd.Parameters.Add("@PostedLength", SqlDbType.VarChar, 19);
			cmd.Parameters.Add("@PostedPercent", SqlDbType.VarChar, 6);
			cmd.Parameters.Add("@uid", SqlDbType.Int);
			cmd.Parameters.Add("@fid", SqlDbType.Int);
			cmd.Parameters[0].Value = filePos.ToString();
			cmd.Parameters[1].Value = postedLength.ToString();
			cmd.Parameters[2].Value = postedPercent;
			cmd.Parameters[3].Value = uid;
			cmd.Parameters[4].Value = fid;
			cmd.Connection.Open();
			cmd.ExecuteNonQuery();
			cmd.Connection.Close();
			return true;
		}

		/// <summary>
		/// 上传完成。将所有相同MD5文件进度都设为100%
		/// </summary>
		public void UploadComplete(string md5)
		{
			string sql = "update xdb_files set PostedLength=FileLength,PostedPercent='100%',PostComplete=1 where FileMD5=@FileMD5";
			SqlConnection con = this.GetCon();
			SqlCommand cmd = con.CreateCommand();
			cmd.CommandText = sql;
			cmd.Parameters.Add("@FileMD5",SqlDbType.VarChar,32);
			cmd.Parameters[0].Value = md5;
			cmd.Connection.Open();
			cmd.ExecuteNonQuery();
			cmd.Connection.Close();
		}

		/// <summary>
		/// 检查相同MD5文件是否有已经上传完的文件
		/// </summary>
		/// <param name="md5"></param>
		public bool HasCompleteFile(string md5)
		{
			//为空
			if (string.IsNullOrEmpty(md5)) return false;

			string sql = "select fid from xdb_files where PostComplete=1 and FileMD5=@FileMD5";
			SqlConnection con = this.GetCon();
			SqlCommand cmd = con.CreateCommand();
			cmd.CommandText = sql;
			cmd.Parameters.Add("@FileMD5", SqlDbType.VarChar, 32);
			cmd.Parameters[0].Value = md5;
			cmd.Connection.Open();
			object ret = cmd.ExecuteScalar();
			cmd.Connection.Close();
			cmd.Dispose();

			return ret != null;
		}

	    public bool HasReachFileLimit(string systemId, string moduleId, string belongId,int fileLimit)
        {
            string sql = "select count(0) from xdb_files where IsDeleted=0 and SystemId=@SystemId and ModuleId=@ModuleId and BelongId=@BelongId;";
            SqlConnection con = this.GetCon();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@SystemId", SqlDbType.NVarChar);
            cmd.Parameters.Add("@ModuleId", SqlDbType.NVarChar);
            cmd.Parameters.Add("@BelongId", SqlDbType.NVarChar);
            cmd.Parameters[0].Value = systemId;
            cmd.Parameters[1].Value = moduleId;
            cmd.Parameters[2].Value = belongId;
            cmd.Connection.Open();
            object ret = cmd.ExecuteScalar();
            cmd.Connection.Close();
            cmd.Dispose();

	        return int.Parse(ret.ToString()) >= fileLimit;
        }

		/// <summary>
		/// 删除一条数据，并不真正删除，只更新删除标识。
		/// </summary>
		/// <param name="uid"></param>
		/// <param name="fid"></param>
		public void Delete(int uid,int fid)
		{
			string sql = "update xdb_files set IsDeleted=1 where uid=@uid and fid=@fid";
			SqlConnection con = this.GetCon();
			SqlCommand cmd = con.CreateCommand();
			cmd.CommandText = sql;
			cmd.Parameters.Add("@uid", SqlDbType.Int);
			cmd.Parameters.Add("@fid", SqlDbType.Int);
			cmd.Parameters[0].Value = uid;
			cmd.Parameters[1].Value = fid;
			cmd.Connection.Open();
			cmd.ExecuteNonQuery();
			cmd.Connection.Close();
		}
	}
}